import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * This class creates  an interface for an agency or an Employer
 * Final Project for TCSS 445.
 * 
 * @author Sanjeev Kamboj
 */
public class SqlDao {
	private Connection my_con;
	
	public SqlDao(final Connection the_con) {
		my_con = the_con;
	}
	
	protected ResultSet getEmployer(final String username) {
		
		ResultSet result = null;
		String str = "Select * From Employer Where Employer.name = ?";
		
		try {
			PreparedStatement statement = my_con.prepareStatement(str);
			statement.setString(1, username);
			
			result = statement.executeQuery();
		} catch (SQLException e) {
			System.err.println("Error in getEmployer.");
			e.printStackTrace();
		}
		
		
		return result;
	}
	protected int getEmpID(final String username) {
		ResultSet result= getEmployer(username);
		int id = -1;
		String str_id = "";
		
		try {
			if (result.next() ) {
			    str_id = result.getString(1);
			    id = Integer.parseInt(str_id);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.err.println("Error in getEmpID");
			e.printStackTrace();
		}
		return id;
		
	}
	
	protected boolean isEmployer(final String username, final String password) {
		ResultSet result = getEmployer(username);
		boolean is_emp = false;
		
		try {
			while(result.next()) {
				if(username.equals(result.getString("Name")) &&
			       password.equals(result.getString("Password"))) {
					
					is_emp = true;
				}
			}
		} catch (SQLException e) {
			System.err.println("Error in isEmployer.");
			e.printStackTrace();
		}
		return is_emp;
	}
	
	protected void insertJobPosting(final String posTitle, final String industry,
									final String openDate, final String closeDate,
									final String minQual, final String jobLocation,
									final double minSalary, final double maxSalary,
									final String howToApply, final int EmpID) {
		
		 
         try {
        	 PreparedStatement statement = my_con.prepareStatement("INSERT INTO JobPosting VALUES (NULL,?,?,?,?,?,?,?,?,?,?)");
             statement.setString(1, posTitle);
			 statement.setString(2, industry);
			 statement.setString(3, openDate);
	         statement.setString(4, closeDate);
	         statement.setString(5, minQual);
	         statement.setString(6, jobLocation);
	         statement.setDouble(7, minSalary);
	         statement.setDouble(8, maxSalary);
	         statement.setString(9, howToApply);
	         statement.setDouble(10, EmpID);
	         
	         statement.executeUpdate();
		} catch (SQLException e) {
			System.err.println("Error in insertJobPosting");
			e.printStackTrace();
		}
        
		
	}
	protected void insertEvent(final String type, final String audience,
			   				   final String objective, final String address,
			   				   final String date, final String time,
			                   final String info) {


		try {
			PreparedStatement statement = my_con.prepareStatement("INSERT INTO Event VALUES (NULL,?,?,?,?,?,?,?)");
			statement.setString(1, type);
			statement.setString(2, audience);
			statement.setString(3, objective);
			statement.setString(4, address);
			statement.setString(5, date);
			statement.setString(6, time);
			statement.setString(7, info);
			
			statement.executeUpdate();
		} catch (SQLException e) {
			System.err.println("Error in insertEvent.");
			e.printStackTrace();
		}


	}
	
	protected void insertEmpEvent(final int empID, final int eventID) {

		try {
			PreparedStatement statement = my_con.prepareStatement("INSERT INTO EmplEventIntersect VALUES (?,?)");
			statement.setString(1, "" + empID);
			statement.setString(2, "" + eventID);
		
			statement.executeUpdate();
		} catch (SQLException e) {
			System.err.println("Error in insertEmpEvent.");
			e.printStackTrace();
		}


	}
	protected int getEventAutoCount() {
		PreparedStatement statement = null;
		ResultSet set = null;
		
		int id = -1;
		
		try {
			
		    String query = "SELECT count(*) from Event";
		    
		    statement = my_con.prepareStatement(query);
		    
		    set = statement.executeQuery();
		    
		    if(set.next()) {
		    	id = Integer.parseInt(set.getString(1));
		    }
			
		} catch (SQLException e) {
			System.err.println("Error in insertEvent.");
			e.printStackTrace();
		}
		
		return id;
	}
	
	protected ResultSet getJobPosting(final int emp_id) {
		PreparedStatement statement = null;
		ResultSet set = null;
		
		try {
			
		    String query = "Select * From JobPosting Where EmployerID = ?";
		    
		    statement = my_con.prepareStatement(query);
		    statement.setString(1, Integer.toString(emp_id));
		    
		    set= statement.executeQuery();
			
		} catch (SQLException e) {
			System.err.println("Error in insertEvent.");
			e.printStackTrace();
		}
		return set;

	}
	
	protected ResultSet getOneJobPost(final int job_id) {
		PreparedStatement statement = null;
		ResultSet set = null;
		
		try {
			
		    String query = "Select * From JobPosting Where JobID = ?";
		    
		    statement = my_con.prepareStatement(query);
		    statement.setString(1, Integer.toString(job_id));
		    
		    set = statement.executeQuery();
		    
			
		} catch (SQLException e) {
			System.err.println("Error in insertEvent.");
			e.printStackTrace();
		}
		return set;
	}
	
	protected ResultSet getOneEvent(final int event_id) {
		PreparedStatement statement = null;
		ResultSet set = null;
		
		try {
			
		    String query = "Select * From Event Where EventID = ?";
		    
		    statement = my_con.prepareStatement(query);
		    statement.setString(1, Integer.toString(event_id));
		    
		    set = statement.executeQuery();
		    
			
		} catch (SQLException e) {
			System.err.println("Error in insertEvent.");
			e.printStackTrace();
		}
		return set;
	}
	
	protected ResultSet getEvent(final int user_ID) {
		PreparedStatement statement = null;
		ResultSet set = null;
		
		try {
			
		    String query = "Select * From Event Where EventID IN(Select EventID " +
		    			   "From EmplEventIntersect Where  EmplEventIntersect.EmployerID = ?) ";
		    
		    statement = my_con.prepareStatement(query);
		    statement.setString(1, Integer.toString(user_ID));
		    
		    set= statement.executeQuery();
			
		} catch (SQLException e) {
			System.err.println("Error in insertEvent.");
			e.printStackTrace();
		}
		return set;

	}
	/*protected ResultSet getEmpEvents(final int EmpID) {
		PreparedStatement statement = null;
		ResultSet set = null;
		
		try {
			
		    String query = "Select * From Event Where EventID = ?";
		    
		    statement = my_con.prepareStatement(query);
		    statement.setString(1, Integer.toString(EventID));
		    
		    set= statement.executeQuery();
			
		} catch (SQLException e) {
			System.err.println("Error in insertEvent.");
			e.printStackTrace();
		}
		return set;

	}*/



}

